<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Mguarantee extends CI_Model {

  public function __construct()
  {
     parent::__construct();
     //Do your magic here
  }

  public function get_partner($sales_id, $user_group_id, $keyword)
  {
    $cluster_id = $this->mglobal->user_cluster($sales_id);
    $this->db->select("tt.territory_name");
    if($user_group_id == 9) {
      $this->db->select("aa.agent_id as channel_id");
      $this->db->join("t_mtr_agent AS aa","aa.territory_id = tt.territory_id");
      $search_1 = "(upper(aa.agent_id) like '%$keyword%' OR upper(tt.territory_name) like '%$keyword%')";
      $where_sales = "(tt.user_id = '$sales_id' or (tt.parent_id = $cluster_id and aa.agent_selling_type = 2) )";
    }
    else {
      $this->db->select("oo.outlet_id as channel_id");
      $this->db->join("t_mtr_outlet AS oo","oo.territory_id = tt.territory_id");
      $search_1 = "(upper(oo.outlet_id) LIKE '%$keyword%' OR upper(tt.territory_name) like '%$keyword%')";
      $where_sales = "tt.user_id = '$sales_id'";
    }
    $this->db->where($where_sales);
    $this->db->where("tt.istatus", 1);
    $this->db->where($search_1);
    $this->db->limit(5);
    $query = $this->db->get("t_mtr_territory AS tt");
    return $query->result_array();
  }

  public function get_frontliners($outlet_id)
  {
    $this->db->select("outlet_frontliner_id, frontliner_name");
    // $this->db->where("istatus", 1 );
    $query = $this->db->get_where("t_mtr_outlet_frontliner", array("outlet_id" => $outlet_id, "istatus" => 1));
    return $query->result_array();
  }

  public function program()
  {
    $query = $this->db->get("v_combo_card_claim_program");
    return $query->result();
  }

  public function validate($partner_id) 
  {
    $this->db->select("f_get_territory_id_by_channel_id('$partner_id')", false);
    $query = $this->db->get()->row()->f_get_territory_id_by_channel_id;
    // echo $query;
    if ($query > 0 ) {
      return true;
    }
    else {
      return false;
    }

  }

  /**
   * Validasi item setelah tombol simpan di tekan
   * Pertama: periksa apakah item sudah pernah di klaim sebelumnya kemudian bandingkan dengan $iccid
   * output dari array_diff() adalah ICCID yang belum pernah diklaim (tapi belum valid)
   * Kedua: Item yang valid, adalah item yang dijual oleh $user_id ke $channel_id
   * lakukan query sekali lagi (valid).
   * @param  string $user_id
   * @param  string $channel_id Agent_ID atau Outlet_ID
   * @param  array  $iccid
   * @return array  valid ICCID
   */
  public function validate_level_2($user_id, $channel_id, $iccid)
  {
     // f_item_waranty_isvalid(varchar,varchar,varchar) --$1=ICCID, $2=SALESID, $3=CHANNEL/PARTNER ID
    $this->db->select("iccid");
    $this->db->select("f_item_waranty_isvalid(iccid, '$user_id', '$channel_id') as valid", false);
    $this->db->where_in("iccid", $iccid );
    $query  = $this->db->get("t_mtr_item");
    $temp_1 = $query->result_array();
    $temp_2 = array(); // just in case if the $temp_1 is empty array
    foreach ($temp_1 as $key) {
      if ($key["valid"] == 'OK') {
        $temp_2[] = $key["iccid"];
      }
    }

    if(count($temp_2)) 
      { return $temp_2; }
    else
      { return array(); }


    ## Function below are depracated ########################################

    // Jika item sudah tersimpan, maka seharusnya $temp tidak kosong. mari kita bandingkan
    // Outpunya adalah array baru, perpotongan antara $iccid dan $temp.
    
    // $unique_iccid = array_diff($iccid, $temp_2);

    // if (count($unique_iccid)) {
    //  $this->db->select("sd.iccid");
    //  $this->db->join("t_trx_sell_in ss","ss.sell_in_id = sd.sell_in_id");
    //  $this->db->where_in("sd.iccid", $unique_iccid, true);
    //  $this->db->where("ss.user_id", $user_id);
    //  $query = $this->db->get('t_trx_sell_in_detail as sd');

    //  return $query->result_array();
    // }
    // else 
    //  return array();
    
    ## End of Depracated ######################################################
  }

  public function valid_item($sales_id, $channel_id, $iccid)
  {
    $this->db->select("sd.iccid, ii.item_code, ii.item_name, ss.created_on, ss.channel_id");
    $this->db->join("t_trx_sell_in ss","ss.sell_in_id = sd.sell_in_id");
    $this->db->join("t_mtr_item as ii","ii.iccid = sd.iccid OR ii.mdn = '$iccid'");
    $this->db->where("sd.iccid", $iccid, true);
    $this->db->where("ss.user_id", $sales_id);
    $this->db->where('ss.channel_id', $channel_id);
    $query = $this->db->get('t_trx_sell_in_detail as sd');
    if ($query->num_rows() > 0 ) 
     {
        $temp = $query->row_array();
        $temp['istatus'] = 'valid';
        return $temp; 
     }
    else

    { return array("istatus" => 'invalid'); }
  }

  public function save($info)
  {
    $channel_id = $info["channel_id"];

    $data = array(
      'user_id'    => $info["user_id"] ,
      'requestor'  => $info["requestor"] ,
      'claim_date' => $info["claim_date"] ,
      'program'    => $info["program"],
      'istatus'    => 1,
      'created_by' => $this->session->userdata('username'),
      'created_on' => date('Y-m-d H:i:s'),
      'acess_via'  => 'web'
      );
    $this->db->select("f_get_territory_id_by_channel_id('$channel_id') as territory_id");
    $data["territory_id"] = $this->db->get()->row()->territory_id;

    $this->db->insert('t_trx_card_claim', $data);
    $claim_id =  $this->db->insert_id();;
    $this->mglobal->saveLog('klaim-kartu-garansi', 'create', $claim_id);
    return $claim_id;
  }

  public function save_detail($valid_iccid, $claim_id)
  {
    $this->db->insert_batch('t_trx_card_claim_detail', $valid_iccid);
    $this->mglobal->saveLog('klaim-kartu-garansi', 'create-detail '.count($valid_iccid).' items' , $claim_id);
  }

  /**
   * Bagian ini untuk menampilkan detail transaksi
   * Query pertama untuk mengambil info header (info transaksi)
   * Query kedua untuk mengambil detail item
   * @param  integer $id ID transaksi
   * @return array object
   */
  public function detail($id)
  {
    $this->db->select("cc.user_id, cc.claim_date, cc.claim_id, uu.user_name, gg.user_group_caption, ll.lookup_desc, of.frontliner_name ");
    $this->db->select("f_get_channel_id_by_territory_id(cc.territory_id) as channel_id");
    $this->db->select("f_get_channel_name(f_get_channel_id_by_territory_id(cc.territory_id)) as channel_name");
    $this->db->select("f_get_territory_path(cc.territory_id) as territory");
    $this->db->join("t_mtr_lookup as ll","to_number(ll.lookup_value , '999999') = cc.program");
    $this->db->join("t_mtr_user as uu","uu.user_id = cc.user_id");
    $this->db->join("t_mtr_user_group as gg","gg.user_group_id = uu.user_group_id");
    $this->db->join("t_mtr_outlet_frontliner as of","of.outlet_frontliner_id  = cast(nullif(cc.requestor,'') as integer)", 'left', TRUE); // left join dengan frontliner
    $this->db->where("ll.lookup_key", 'card_claim_program' );
    $this->db->where("cc.claim_id", $id);
    $query = $this->db->get("t_trx_card_claim as cc");
    $data['header'] = $query->row();
    $query->free_result();

    $this->db->select("cd.iccid, ii.mdn, ii.item_name, gg.item_group_name");
    $this->db->join("t_mtr_item as ii","ii.iccid = cd.iccid");
    $this->db->join("t_mtr_item_group as gg","gg.item_group_id = ii.item_group_id");
    $this->db->where("cd.claim_id", $id);
    $query = $this->db->get("t_trx_card_claim_detail as cd");
    $data["detail"] = $query->result();
    $query->free_result();
    return $data;
  }

  /**
   * Ini query untuk menampilkan data transaksi klaim kartu garansi (KKG)
   * Seperti biasa ada parameter $cluster_id
   * @param  integer $cluster_id
   * @return array
   */
  public function get_all_guarantee($cluster_id)
  {
    $sIndexColumn = "claim_id"; //index column untuk kecepatan akses data
    $defaultOrder = "0"; //default order berdasarkan field yg ada pada $aColumns
    $userlogin = $this->session->userdata('username'); //user credentials
    $flag = '';
    // $aColumns = array("agent_id", "agent_name", "agent_address", "image_path", "lon", "lat"); //field2 yg akan ditampilkan pada dataTables
    $aColumns = array("claim_id", "claim_date", "user_name", "channel_name", "frontliner_name"); //field2 yg akan ditampilkan pada dataTables
    $inputQuery = "SELECT ".str_replace(" , ", " ", implode(", ", $aColumns))."
    FROM v_ssp_guarantee_2 where cluster_id = $cluster_id";
    return $this->serversidedatatables->executeServerSideProcess($defaultOrder, $flag, $userlogin, $inputQuery, $aColumns, $sIndexColumn, 'desc');
  }
}

/* End of file mguarantee.php */
/* Location: ./application/controllers/mguarantee.php */